package com.dameng;

import com.dameng.domain.InstanceEfficiency;
import com.dameng.domain.InstanceInfo;
import com.dameng.domain.LoadProfileEntry;
import com.dameng.domain.TopSqlByElapsedTime;

import java.sql.*;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
import java.util.Map;

/**
 * @author gaoyuan
 * @date 2025-04-07$
 */
public class DatabaseUtil {

/*    public static Connection getConnection() throws SQLException, ClassNotFoundException {
        Class.forName("dm.jdbc.driver.DmDriver");
        return DriverManager.getConnection("jdbc:dm://81.70.105.201:5236", "AWR", "123456");
    }*/

    public static Connection getConnection() throws SQLException, ClassNotFoundException {
        Class.forName("dm.jdbc.driver.DmDriver");
        return DriverManager.getConnection("jdbc:dm://192.168.112.166:5237", "AWR", "Dameng123#");
    }

    public static void insertTopSqlList(Connection conn, List<TopSqlByElapsedTime> topSqlList) throws SQLException {
        String insertSql = "INSERT INTO top_sql_by_elapsed_time (" +
                "db_id, instance, file_time, elapsed_time_seconds, executions, " +
                "elapsed_time_per_exec, total_percentage, cpu_percentage, io_percentage, " +
                "sql_id, sql_module, sql_text" +
                ") VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

        try (PreparedStatement stmt = conn.prepareStatement(insertSql)) {
            for (TopSqlByElapsedTime entry : topSqlList) {
                stmt.setString(1, entry.getDbId());
                stmt.setString(2, entry.getInstance());
                stmt.setString(3, entry.getFileTime());
                stmt.setBigDecimal(4, entry.getElapsedTimeSeconds());
                stmt.setObject(5, entry.getExecutions()); // 用 setInt 也可以
                stmt.setBigDecimal(6, entry.getElapsedTimePerExec());
                stmt.setBigDecimal(7, entry.getTotalPercentage());
                stmt.setBigDecimal(8, entry.getCpuPercentage());
                stmt.setBigDecimal(9, entry.getIoPercentage());
                stmt.setString(10, entry.getSqlId());
                stmt.setString(11, entry.getSqlModule());
                stmt.setString(12, entry.getSqlText());

                stmt.executeUpdate();
            }
        }
    }



    public static void insertLoadProfileList(Connection conn, List<LoadProfileEntry> loadProfileEntryList) throws SQLException {

        String insertSql = "INSERT INTO load_profile " +
                "(db_id, instance, metric_name, per_second, per_transaction, per_exec, per_call,file_name) " +
                "VALUES (?, ?, ?, ?, ?, ?, ?, ?)";

        try (
                PreparedStatement insertStmt = conn.prepareStatement(insertSql)
        ) {
            for (LoadProfileEntry entry : loadProfileEntryList) {
                insertStmt.setString(1, entry.getDbId());
                insertStmt.setString(2, entry.getInstance());
                insertStmt.setString(3, entry.getMetricName());
                insertStmt.setBigDecimal(4, entry.getPerSecond());
                insertStmt.setBigDecimal(5, entry.getPerTransaction());
                insertStmt.setBigDecimal(6, entry.getPerExec());
                insertStmt.setBigDecimal(7, entry.getPerCall());
                insertStmt.setString(8, entry.getFileTime());
                insertStmt.executeUpdate();
            }
        }
    }

    public static void insertInstanceEfficiency(Connection conn, InstanceEfficiency info) throws SQLException {

        try {


        String insertSql = "INSERT INTO instance_efficiency (" +
                "db_id, instance, buffer_nowait, redo_nowait, buffer_hit, in_memory_sort, library_hit, " +
                "soft_parse, execute_to_parse, latch_hit, parse_cpu_to_elapsed, non_parse_cpu,file_name) " +
                "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

        try (PreparedStatement stmt = conn.prepareStatement(insertSql)) {
            stmt.setString(1, info.getDbId());
            stmt.setString(2, info.getInstance());
            stmt.setBigDecimal(3, info.getBufferNowait());
            stmt.setBigDecimal(4, info.getRedoNowait());
            stmt.setBigDecimal(5, info.getBufferHit());
            stmt.setBigDecimal(6, info.getInMemorySort());
            stmt.setBigDecimal(7, info.getLibraryHit());
            stmt.setBigDecimal(8, info.getSoftParse());
            stmt.setBigDecimal(9, info.getExecuteToParse());
            stmt.setBigDecimal(10, info.getLatchHit());
            stmt.setBigDecimal(11, info.getParseCpuToElapsed());
            stmt.setBigDecimal(12, info.getNonParseCpu());
            stmt.setString(13, info.getFileTime());
            stmt.executeUpdate();
        }
        }catch (SQLException e){
            System.out.println("插入效率指标失败: dbId=" + info.getDbId() + ", instance=" + info.getInstance());

        }

        //System.out.println("效率指标插入成功: dbId=" + info.getDbId() + ", instance=" + info.getInstance());
    }


    public static void insertInstanceInfo( Connection conn,InstanceInfo info) throws SQLException, ClassNotFoundException {

        // 1. 判断是否已存在
        String checkSql = "SELECT 1 FROM instance_info WHERE inst_num = ? AND db_id = ?";
        try (PreparedStatement checkStmt = conn.prepareStatement(checkSql)) {
            checkStmt.setString(1, info.getInstNum());
            checkStmt.setString(2, info.getDbId());

            try (ResultSet rs = checkStmt.executeQuery()) {
                if (rs.next()) {
                    // 已存在，跳过插入
                   // System.out.println("记录已存在，跳过插入: inst_num=" + info.getInstNum() + ", db_id=" + info.getDbId());
                    return;
                }
            }
        }

        // 2. 插入语句
        String insertSql = "INSERT INTO instance_info (inst_num, db_name, db_id, instance, startup_time, release, rac, " +
                "host_name, platform, cpus, cores, sockets, memory_gb) " +
                "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
        try (PreparedStatement stmt = conn.prepareStatement(insertSql)) {
            stmt.setString(1, info.getInstNum());
            stmt.setString(2, info.getDbName());
            stmt.setString(3, info.getDbId());
            stmt.setString(4, info.getInstance());
            stmt.setString(5, info.getStartupTime());
            stmt.setString(6, info.getRelease());
            stmt.setString(7, info.getRac());
            stmt.setString(8, info.getHostName());
            stmt.setString(9, info.getPlatform());
            stmt.setString(10, info.getCpus());
            stmt.setString(11, info.getCores());
            stmt.setString(12, info.getSockets());
            stmt.setString(13, info.getMemoryGb());
            stmt.executeUpdate();
        }

        //System.out.println("插入成功: inst_num=" + info.getInstNum() + ", db_id=" + info.getDbId());
    }



}
